# 编程实现：使用openpyxl在excel中写入10列2000行的数据，然后使用openpyxl读取出来并使用pymysql写入到数据库中
import pymysql
import openpyxl

class DButils:
    def __init__(self,host,user,password,database,port=3306,charset='utf8'):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.charset = charset

    def update(self,sql,data = None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql,data)
                db.commit()
                print('更新成功')
        except Exception as e:
            print('更新失败',e)
        finally:
            db.close()

    def select(self,sql,data = None):
        db = pymysql.connect(
            host=self.host,
            user=self.user,
            password=self.password,
            database=self.database,
            port=self.port,
            charset=self.charset
        )
        try:
            with db.cursor() as cursor:
                cursor.execute(sql,data)
                result = cursor.fetchall()
                return result
        except Exception as e:
            return '查询失败',e
        finally:
            db.close()


class ReadExcel:
    @staticmethod
    def read_excel(file_name,sheet_name):
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        data = [[sheet.cell(row=i,column=j).value for j in range(1,sheet.max_column+1)] for i in range(1,sheet.max_row+1)]
        return data
    @staticmethod
    def read_excel1(file_name,sheet_name):
        workbook = openpyxl.load_workbook(file_name)
        sheet = workbook[sheet_name]
        data = []
        for i in range(1,sheet.max_row+1):
            row = []
            for j in range(1,sheet.max_column+1):
                row.append(sheet.cell(row=i,column=j).value)
            data.append(row)
        return data





if __name__ == '__main__':
    db = DButils('127.0.0.1','root','','test')
    sql = 'DROP TABLE IF EXISTS user_info'
    db.update(sql)
    sql = "CREATE TABLE `user_info` (`id` int(11) ,`idcard` varchar(50) ,`username` varchar(50) ,`realname` varchar(50) ,`pwd` varchar(50) ,`telphone` varchar(12) ,`email` varchar(100) ,`age` int(11) ,`sex` varchar(20) ,`address` varchar(200) ) ;"
    db.update(sql)
    workbook = openpyxl.Workbook('user.xlsx')
    worksheet = workbook.create_sheet(title='one sheet')
    workbook.save('user.xlsx')
    workbook = openpyxl.load_workbook('user.xlsx')
    worksheet = workbook['one sheet']
    for i in range(1,2001):
        worksheet.append(range(1,11))
    workbook.save('user.xlsx')
    data = ReadExcel.read_excel1('user.xlsx','one sheet')

    print(len(data))
    for i in data:
        j = tuple(i)
        sql = f'insert into user_info values {j}'
        db.update(sql)
